package com.redsoft.spirit.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.redsoft.spirit.dao.ComCodeTableRepository;
import com.redsoft.spirit.entity.ComCodeTable;
import com.redsoft.spirit.entity.Menu;
import com.redsoft.spirit.entity.TreeNode;
import com.redsoft.spirit.util.BeanUtils;

/**
 *	码表Service
 * <pre>
 * 	码表Service
 * </pre>
 * 
 * @author 李晓东
 * 
 * 2017.04.25
 * @since 1.0
 */
@Service
@Transactional
public class ComCodeTableService {
	
	@Autowired
	private ComCodeTableRepository codeTableRepository;
	
	@Value("${jdbc.db-type}")
	private String dbType;
	
	/**
	 * 根据内部名返回码表详情
	 * 
	 * <pre>
	 * 根据内部名返回码表详情
	 * </pre>
	 * 
	 * @author 李晓东
	 * @param innerName
	 * @return
	 */
	public ComCodeTable getByInnerName(String innerName) {
		return codeTableRepository.findByInnerName(innerName);
	}
	
	/**
	 * 根据父innerName获取列表详情
	 * 
	 * <pre>
	 * 根据父innerName获取列表详情
	 * </pre>
	 * 
	 * @author 李晓东
	 * @param innerName
	 * @return
	 */
	public List<ComCodeTable> getByParentInnerName(String innerName) {
		ComCodeTable codeTable = getByInnerName(innerName);
		return codeTableRepository.findByParentId(codeTable.getId());
	}

	/**
	 * 获取码表管理树
	 * 
	 * <pre>
	 * 获取码表管理树
	 * </pre>
	 * 
	 * @author 杨雷
	 * @return
	 */
	public List<TreeNode> codeTableTree() {
		
		List<TreeNode> trees = new ArrayList<>();
		
		//先查询出所有的码表项，然后遍历，创建出对应的treeNode项
//		String sql = "SELECT * FROM com_code_table WHERE FIND_IN_SET(id,getSonCodeTable(0))";
		String sql = "SELECT * FROM com_code_table where parent_id = '0'";
		//如果是mysql的话，则使用存储过程完成这段返回树形结构的数据
//		if("mysql".equals(dbType.toLowerCase())) {
//			sql = "SELECT * FROM com_code_table WHERE FIND_IN_SET(id,getSonCodeTable(0))";
//
//		} else if ("oracle".equals(dbType.toLowerCase())) {
//			sql = "select * from com_code_table start with id = '0' connect by prior id = PARENT_ID";
//		} else if("sqlserver".equals(dbType.toLowerCase())){
//			sql = "with code_temp as ( select *,0 AS LevelID from com_code_table WHERE id=  '0' UNION ALL SELECT a.*,(LevelID + 1) AS LevelID "
//					+ "from com_code_table a INNER JOIN code_temp b ON a.PARENT_ID = b.id) SELECT * FROM code_temp";
//		}
		
		List <ComCodeTable> codeTables = codeTableRepository.sqlQuery(sql);
		
		trees = initTree(codeTables);
		
//		List <ComCodeTable> codeTables = codeTableRepository.findByItemLevel(1);
//		
//		for (ComCodeTable codeTable : codeTables) {
//			TreeNode node = tree(codeTable, "1");
//			trees.add(node);
//		}

		return trees;
	}
	
	/**
	 * 把Java的list数组改成tree类型数组
	 * 
	 * <pre>
	 * 把Java的list数组改成tree类型数组
	 * </pre>
	 * 
	 * @author 杨雷
	 * @param codeTables
	 * @return
	 */
	private List<TreeNode> initTree(List<ComCodeTable> codeTables) {

		List<TreeNode> trees = new ArrayList<>();
		Map<String, TreeNode> map = new HashMap<>();
		
		for (ComCodeTable codeTable : codeTables) {
			TreeNode node = new TreeNode();
			node.setId(codeTable.getId());
			node.setLabel(codeTable.getItemName());
			node.setChildren(new ArrayList<TreeNode>());
			node.setParentId(codeTable.getParentId());
			
			map.put(node.getId(), node);	
		}
		
		//当codeTable的parentId从 map中可以获取到时候，把这个codeTable对应的map中的node取出来，放到parentId的node的children中
		for (ComCodeTable codeTable : codeTables) {
			if(map.containsKey(codeTable.getParentId())) {
				map.get(codeTable.getParentId()).getChildren().add(map.get(codeTable.getId()));
//				map.remove(codeTable.getId());
			}
		}
		
		for (TreeNode treeNode : map.values()) {
			if ("0".equals(treeNode.getParentId())) {
				trees.add(treeNode);
			}
		}
		return trees;
	}

	/**
	 * 递归查询码表树
	 * 
	 * <pre>
	 * 递归查询码表树
	 * </pre>
	 * 
	 * @author 杨雷
	 * @param codeTable
	 * @param userId 
	 * @return
	 */
	private TreeNode tree(ComCodeTable codeTable, String userId) {
		TreeNode node = new TreeNode();
		node.setId(codeTable.getId());
		node.setLabel(codeTable.getItemName());
		
		List<ComCodeTable> codeTables = new ArrayList<>();
		
		//如果是 超级管理员则查询全部，如果不是则查询这个人所拥有的所有菜单
		codeTables = codeTableRepository.findByParentIdAndItemValueIsNull(codeTable.getId());
		
		List<TreeNode> children = new ArrayList<>();
		
		for (ComCodeTable codeTableChild : codeTables) {
			TreeNode nodeChild = tree(codeTableChild, userId);
			children.add(nodeChild);
		}
		node.setChildren(children);
		return node;
	}

	/**
	 * 根据ID获取一条记录
	 * 
	 * @param id
	 * @return
	 */
	public ComCodeTable getById(String id) {
		return codeTableRepository.findOne(id);
	}

	/**
	 * 新增或者修改
	 * 
	 * <pre>
	 * 新增或者修改
	 * </pre>
	 * 
	 * @author 杨雷
	 * @param codeTable
	 * @throws Exception 
	 */
	public void save(ComCodeTable codeTable) throws Exception {
		if("".equals(codeTable.getId())) {
			ComCodeTable codeTableOld = codeTableRepository.findByInnerName(codeTable.getInnerName());
			
			if(codeTableOld != null) {
				throw new Exception("唯一标识不能重复");
			}
			codeTableRepository.save(codeTable);		
		} else {
			ComCodeTable codeTableOld = codeTableRepository.findById(codeTable.getId());
			BeanUtils.copyBeanNotNull2Bean(codeTable, codeTableOld);
			codeTableRepository.save(codeTableOld);
		}
	}

	/**
	 * 删除的时候把选择的id和所有子项都删除掉，先查询出所选择的id的包含自己的子项id
	 * 
	 * <pre>
	 * 删除的时候把选择的id和所有子项都删除掉，先查询出所选择的id的包含自己的子项id
	 * </pre>
	 * 
	 * @author 杨雷
	 * @param id
	 */
	public void deleteAllById(String id) {
//		String sql = "SELECT * FROM com_code_table WHERE FIND_IN_SET(id,getSonCodeTable(?))";
		
		String sql = "";
		
		//如果是mysql的话，则使用存储过程完成这段返回树形结构的数据
		if("mysql".equals(dbType.toLowerCase())) {
			sql = "SELECT * FROM com_code_table WHERE FIND_IN_SET(id,getSonCodeTable(?))";
		} else if ("oracle".equals(dbType.toLowerCase())) {
			sql = "select * from com_code_table start with id = ? connect by prior id = PARENT_ID";
		} else if("sqlserver".equals(dbType.toLowerCase())){
			sql = "with code_temp as ( select *,0 AS LevelID from com_code_table WHERE id=  ? UNION ALL SELECT a.*,(LevelID + 1) AS LevelID"
					+ " from com_code_table a INNER JOIN code_temp b ON a.PARENT_ID = b.id) SELECT * FROM code_temp";
		}
		
		List <ComCodeTable> codeTables = codeTableRepository.sqlQuery(sql, id);
		
		for (ComCodeTable comCodeTable : codeTables) {
			codeTableRepository.delete(comCodeTable);
		}
	}

	/**
	 * 根据id查询子节点
	 * 
	 * <pre>
	 * 根据id查询子节点
	 * </pre>
	 * 
	 * @author 杨雷
	 * @param id
	 * @return
	 */
	public List<ComCodeTable> getByParentId(String id) {
		return codeTableRepository.findByParentId(id);
	}
}
